<?php
class Z1sinhviensController extends AppController {

	var $name = 'Z1sinhviens';
	var $uses =array('Z1sinhvien','Phanloaisv','Nganh','Khoahoc','Dantoc','Tinhtp','Xeploai','Namhoc','Hocky','Chidoansv');
	var $helpers = array('Form','Html','Js');
	var $components = array('RequestHandler');	
	
    function danhsach(){
        $result=$this->Khoahoc->find('all',array('recursive'=>-1));
        $this->set(compact('result'));
    }
    
    function chidoan($khoahoc_id=null){
        $this->layout='ajax';
        $chidoan=$this->Chidoansv->find('all',array('recursive'=>-1,'conditions'=>array('khoahoc_id'=>$khoahoc_id),'order'=>'tenchidoan asc'));
        $this->set(compact('chidoan'));
    }
    
	function index($chidoan_id=null) {
		$this->Z1sinhvien->recursive = 0;
		$this->set('z1sinhviens', $this->paginate(array('chidoansv_id'=>$chidoan_id)));
	}
    
	function view($id = null) {
		if (!$id) {
			$this->Session->setFlash(__('Invalid z1sinhvien', true));
			$this->redirect(array('action' => 'index'));
		}
		$this->set('z1sinhvien', $this->Z1sinhvien->read(null, $id));
	}

	function add() {
		if (!empty($this->data)) {
			$this->Z1sinhvien->create();
			if ($this->Z1sinhvien->save($this->data)) {
				$this->Session->setFlash(__('The z1sinhvien has been saved', true));
				$this->redirect(array('action' => 'index'));
			} else {
				$this->Session->setFlash(__('The z1sinhvien could not be saved. Please, try again.', true));
			}
		}
		$tongiaos = $this->Z1sinhvien->Tongiao->find('list');
		$phuongxas = $this->Z1sinhvien->Phuongxa->find('list');
		$dantocs = $this->Z1sinhvien->Dantoc->find('list');
		$tinhtps = $this->Z1sinhvien->Tinhtp->find('list');
		$chidoansvs = $this->Z1sinhvien->Chidoansv->find('list');
		$khoahocs = $this->Z1sinhvien->Khoahoc->find('list');
		$khoas = $this->Z1sinhvien->Khoa->find('list');
		$this->set(compact('tongiaos', 'phuongxas', 'dantocs', 'tinhtps', 'chidoansvs', 'khoahocs', 'khoas'));
	}

	function edit($id = null) {
		if (!$id && empty($this->data)) {
			$this->Session->setFlash(__('Invalid z1sinhvien', true));
			$this->redirect(array('action' => 'index'));
		}
		if (!empty($this->data)) {
			if ($this->Z1sinhvien->save($this->data)) {
				$this->Session->setFlash(__('The z1sinhvien has been saved', true));
				$this->redirect(array('action' => 'index'));
			} else {
				$this->Session->setFlash(__('The z1sinhvien could not be saved. Please, try again.', true));
			}
		}
		if (empty($this->data)) {
			$this->data = $this->Z1sinhvien->read(null, $id);
		}
		$tongiaos = $this->Z1sinhvien->Tongiao->find('list');
		$phuongxas = $this->Z1sinhvien->Phuongxa->find('list');
		$dantocs = $this->Z1sinhvien->Dantoc->find('list');
		$tinhtps = $this->Z1sinhvien->Tinhtp->find('list');
		$chidoansvs = $this->Z1sinhvien->Chidoansv->find('list');
		$khoahocs = $this->Z1sinhvien->Khoahoc->find('list');
		$khoas = $this->Z1sinhvien->Khoa->find('list');
		$this->set(compact('tongiaos', 'phuongxas', 'dantocs', 'tinhtps', 'chidoansvs', 'khoahocs', 'khoas'));
	}

	function delete($id = null) {
		if (!$id) {
			$this->Session->setFlash(__('Invalid id for z1sinhvien', true));
			$this->redirect(array('action'=>'index'));
		}
		if ($this->Z1sinhvien->delete($id)) {
			$this->Session->setFlash(__('Z1sinhvien deleted', true));
			$this->redirect(array('action'=>'index'));
		}
		$this->Session->setFlash(__('Z1sinhvien was not deleted', true));
		$this->redirect(array('action' => 'index'));
	}
	
	function searchsv($name=null)
	{
		//$this->Cookie->write('thucookie',$a,300);
		//debug($this->Cookie->read('thucookie'));
		$isajax = 0;
		if($this->RequestHandler->isAjax())
		{
			$isajax=1;
			$cotdachon = $this->data['Z1sinhvien']['dscol'];
			
			if(!empty($cotdachon))
			{
				$cotdachon = array_flip($cotdachon);
				$cothienthi =array_diff_key($this->Z1sinhvien->dscol,array_diff_key($this->Z1sinhvien->dscol,$cotdachon));
			}
			else
			{
				$cothienthi = $this->Z1sinhvien->dscol;
			}
			
			$conditions = array();
			if($this->data['Z1sinhvien']['hoten']!=null)
				$conditions['hoten LIKE']='%'.$this->data['Z1sinhvien']['hoten'].'%';
			if($this->data['Z1sinhvien']['mssv']!=null)
				$conditions['mssv LIKE']='%'.$this->data['Z1sinhvien']['mssv'].'%';
			if($this->data['Z1sinhvien']['tendantoc']!=null)
				$conditions['Z1sinhvien.dantoc_id']=$this->data['Z1sinhvien']['tendantoc'];
			if($this->data['Z1sinhvien']['nganh']!=null)
				$conditions['Z1sinhvien.nganh_id']=$this->data['Z1sinhvien']['nganh'];
			if($this->data['Z1sinhvien']['tentinhtp']!=null)
				$conditions['Z1sinhvien.tinhtp_id']=$this->data['Z1sinhvien']['tentinhtp'];
			if($this->data['Z1sinhvien']['khoahoc']!=null)
				$conditions['Z1sinhvien.khoahoc_id']=$this->data['Z1sinhvien']['khoahoc'];
			// doan or dang	
			if($this->data['Z1sinhvien']['doituong']!=null)
				if($this->data['Z1sinhvien']['doituong']==0)
				{
					//$cond1['AND']=array('ngayvaodoan !='=>null,'ngayvaodoan !='=>'0000-00-00');
					//$cond2['OR']=array();
					$conditions['AND']=array(array('ngayvaodoan !='=>null),
											array('ngayvaodoan !='=>'0000-00-00'),
											'OR'=>array(
											array('ngayvaodang'=>null),
											array('ngayvaodang'=>'0000-00-00')));
				}
				else
				{
					$conditions['AND']=array(array('ngayvaodoan !='=>null),
											array('ngayvaodoan !='=>'0000-00-00'),
											array('ngayvaodang !='=>null),
											array('ngayvaodang !='=>'0000-00-00'));
				}
				
			if(!empty($this->data['Z1sinhvien']['hocky']) && !empty($this->data['Z1sinhvien']['xeploai']))
			{
				$conditions['Phanloaisv.hocky_id'] = $this->data['Z1sinhvien']['hocky'];
				$conditions['Phanloaisv.xeploai_id'] = $this->data['Z1sinhvien']['xeploai'];
				$conditions['Phanloaisv.duyet']=1;
				$joins =array(array('table' => 'Phanloaisvs'
            						,'type' => 'INNER'
            						,'alias' => 'Phanloaisv'
            						,'conditions' => array(
                					'Z1sinhvien.id = Phanloaisv.sinhvien_id')));						
			}
			
			if($this->data['Z1sinhvien']['phai']!=null)
				$conditions['phai']=$this->data['Z1sinhvien']['phai'];
			$order = array($this->data['Z1sinhvien']['sapxep']=>$this->data['Z1sinhvien']['loaixs']);
			$name ='datasearch'.strtotime(date('h:i:s'));
			$this->Session->write($name,$this->data);
			//$this->Cookie->write('datasearchc',$this->data['Z1sinhvien'],600);
			$this->Z1sinhvien->recursive=1;
			if(isset($joins))
				$this->paginate = array('conditions'=>$conditions,'order'=>$order,'joins'=>$joins);
			else
				$this->paginate = array('conditions'=>$conditions,'order'=>$order);
			$this->set('sinhviens',$this->paginate());
			$this->set(compact('cothienthi','name'));
		}
		else
		{
			if(!empty($this->params['named']['page']) && $name)
			{
				$this->data = $this->Session->read($name);
				//$this->data['Z1sinhvien'] = $this->Cookie->read('datasearchc');
				if(!empty($this->data))
					{
					$cotdachon = $this->data['Z1sinhvien']['dscol'];
					
					if(!empty($cotdachon))
					{
						$cotdachon = array_flip($cotdachon);
						$cothienthi =array_diff_key($this->Z1sinhvien->dscol,array_diff_key($this->Z1sinhvien->dscol,$cotdachon));
					}
					else
					{
						$cothienthi = $this->Z1sinhvien->dscol;
					}
					
					$conditions = array();
					if($this->data['Z1sinhvien']['hoten']!=null)
						$conditions['hoten LIKE']='%'.$this->data['Z1sinhvien']['hoten'].'%';
					if($this->data['Z1sinhvien']['mssv']!=null)
						$conditions['mssv LIKE']='%'.$this->data['Z1sinhvien']['mssv'].'%';
					if($this->data['Z1sinhvien']['tendantoc']!=null)
						$conditions['Z1sinhvien.dantoc_id']=$this->data['Z1sinhvien']['tendantoc'];
					if($this->data['Z1sinhvien']['nganh']!=null)
						$conditions['Z1sinhvien.nganh_id']=$this->data['Z1sinhvien']['nganh'];
					if($this->data['Z1sinhvien']['tentinhtp']!=null)
						$conditions['Z1sinhvien.tinhtp_id']=$this->data['Z1sinhvien']['tentinhtp'];
					if($this->data['Z1sinhvien']['khoahoc']!=null)
						$conditions['Z1sinhvien.khoahoc_id']=$this->data['Z1sinhvien']['khoahoc'];
					// doan or dang	
					if($this->data['Z1sinhvien']['doituong']!=null)
						if($this->data['Z1sinhvien']['doituong']==0)
						{
							$conditions['AND']=array(array('ngayvaodoan !='=>null),
													array('ngayvaodoan !='=>'0000-00-00'),
													'OR'=>array(
													array('ngayvaodang'=>null),
													array('ngayvaodang'=>'0000-00-00')));
						}
						else
						{
							$conditions['AND']=array(array('ngayvaodoan !='=>null),
													array('ngayvaodoan !='=>'0000-00-00'),
													array('ngayvaodang !='=>null),
													array('ngayvaodang !='=>'0000-00-00'));
						}
							
					if($this->data['Z1sinhvien']['phai']!=null)
						$conditions['phai']=$this->data['Z1sinhvien']['phai'];
					if(isset($this->data['Z1sinhvien']['sapxep']))
						$order = array($this->data['Z1sinhvien']['sapxep']=>$this->data['Z1sinhvien']['loaixs']);
					else
						$order = array('mssv'=>'ASC');
					
					if(!empty($this->data['Z1sinhvien']['hocky']) && !empty($this->data['Z1sinhvien']['xeploai']))
					{
						$conditions['Phanloaisv.hocky_id'] = $this->data['Z1sinhvien']['hocky'];
						$conditions['Phanloaisv.xeploai_id'] = $this->data['Z1sinhvien']['xeploai'];
						$conditions['Phanloaisv.duyet']=1;
						$joins =array(array('table' => 'Phanloaisvs'
		            						,'type' => 'INNER'
		            						,'alias' => 'Phanloaisv'
		            						,'conditions' => array(
		                					'Z1sinhvien.id = Phanloaisv.sinhvien_id')));						
					}
					
					if($this->data['Z1sinhvien']['phai']!=null)
						$conditions['phai']=$this->data['Z1sinhvien']['phai'];
					$this->Z1sinhvien->recursive=1;
					if(isset($joins))
						$this->paginate = array('conditions'=>$conditions,'order'=>$order,'joins'=>$joins);
					else
						$this->paginate = array('conditions'=>$conditions,'order'=>$order);
						$this->set('sinhviens',$this->paginate());
					}
			}
			$this->Dantoc->displayField='tendantoc';
			$dantocs = $this->Dantoc->find('list');
			$this->Tinhtp->displayField='tentinhtp';
			$quequans = $this->Tinhtp->find('list');
			$dscol = $this->Z1sinhvien->dscol;
			$this->Nganh->displayField='tennganh';
			$nganhs = $this->Nganh->find('list',array('conditions'=>array('tennganh !='=>null)));
			$this->Khoahoc->displayField='nambatdau';
			$khoahocs = $this->Khoahoc->find('list');
			$this->Xeploai->displayField = 'tenloai';
			$xeploais = $this->Xeploai->find('list');
			$this->Namhoc->displayField='namhoc';
			$namhocs = $this->Namhoc->find('list');
			if(!empty($this->data['Z1sinhvien']['namhoc']))
			{
				$this->Hocky->displayField='tenhocky';
				$hockys = $this->Hocky->find('list',array('conditions'=>array('namhoc_id'=>$this->data['Z1sinhvien']['namhoc'])));
			}
			$this->set(compact('dantocs','quequans','dscol','nganhs','khoahocs','cothienthi','namhocs','xeploais','hockys','name'));
		}
		$this->set(compact('isajax'));
	}

	function layhocky($ch=0)
	{
		$this->layout = 'ajax';
		$namhoc = $this->data['Z1sinhvien']['namhoc'];
		$this->Hocky->displayField='tenhocky';
		$hockys = $this->Hocky->find('list',array('conditions'=>array('namhoc_id'=>$namhoc)));
		$this->set(compact('hockys'));
		if($ch==1)
			$this->render('layhockydiem');
	}

	function xuatexcel($name=null)
	{
		$this->layout = '';
		$this->data = $this->Session->read($name);
		if(!empty($this->data))
		{
			$cotdachon = $this->data['Z1sinhvien']['dscoldiem'];
			if(!empty($cotdachon))
			{
				$cotdachon = array_flip($cotdachon);
				$cothienthi =array_diff_key($this->Z1sinhvien->dscoldiem,array_diff_key($this->Z1sinhvien->dscoldiem,$cotdachon));
			}
			else
			{
				$cothienthi = $this->Z1sinhvien->dscoldiem;
			}
			$conditions = array();
			if($this->data['Z1sinhvien']['hoten']!=null)
				$conditions['hoten LIKE']='%'.$this->data['Z1sinhvien']['hoten'].'%';
			if($this->data['Z1sinhvien']['mssv']!=null)
				$conditions['mssv LIKE']='%'.$this->data['Z1sinhvien']['mssv'].'%';
			if($this->data['Z1sinhvien']['tendantoc']!=null)
				$conditions['Z1sinhvien.dantoc_id']=$this->data['Z1sinhvien']['tendantoc'];
			if($this->data['Z1sinhvien']['nganh']!=null)
				$conditions['Z1sinhvien.nganh_id']=$this->data['Z1sinhvien']['nganh'];
			if($this->data['Z1sinhvien']['tentinhtp']!=null)
				$conditions['Z1sinhvien.tinhtp_id']=$this->data['Z1sinhvien']['tentinhtp'];
			if($this->data['Z1sinhvien']['khoahoc']!=null)
				$conditions['Z1sinhvien.khoahoc_id']=$this->data['Z1sinhvien']['khoahoc'];
			// doan or dang	
			if($this->data['Z1sinhvien']['doituong']!=null)
				if($this->data['Z1sinhvien']['doituong']==0)
				{
					$conditions['AND']=array(array('ngayvaodoan !='=>null),
											array('ngayvaodoan !='=>'0000-00-00'),
											'OR'=>array(
											array('ngayvaodang'=>null),
											array('ngayvaodang'=>'0000-00-00')));
				}
				else
				{
					$conditions['AND']=array(array('ngayvaodoan !='=>null),
											array('ngayvaodoan !='=>'0000-00-00'),
											array('ngayvaodang !='=>null),
											array('ngayvaodang !='=>'0000-00-00'));
				}
				
			if($this->data['Z1sinhvien']['phai']!=null)
				$conditions['phai']=$this->data['Z1sinhvien']['phai'];
			if(isset($this->data['Z1sinhvien']['sapxep']))
				$order = array($this->data['Z1sinhvien']['sapxep']=>$this->data['Z1sinhvien']['loaixs']);
			else
				$order = array('mssv'=>'ASC');
			
			if(!empty($this->data['Z1sinhvien']['hocky']) && !empty($this->data['Z1sinhvien']['xeploai']))
			{
				$conditions['Phanloaisv.hocky_id'] = $this->data['Z1sinhvien']['hocky'];
				$conditions['Phanloaisv.xeploai_id'] = $this->data['Z1sinhvien']['xeploai'];
				$conditions['Phanloaisv.duyet']=1;
				$joins =array(array('table' => 'Phanloaisvs'
		          						,'type' => 'INNER'
		           						,'alias' => 'Phanloaisv'
		           						,'conditions' => array(
		               					'Z1sinhvien.id = Phanloaisv.sinhvien_id')));						
			}
				
			if($this->data['Z1sinhvien']['phai']!=null)
					$conditions['phai']=$this->data['Z1sinhvien']['phai'];
				$this->Z1sinhvien->recursive=1;
			if(isset($joins))
				$arg = array('conditions'=>$conditions,'order'=>$order,'joins'=>$joins);
			else
				$arg = array('conditions'=>$conditions,'order'=>$order);
			
			$sinhviens = $this->Z1sinhvien->find('all',$arg);
			$this->set(compact('sinhviens','cothienthi'));
		}
	}

	function searchdiemsv($name=null)
	{
		$isajax = 0;
		if($this->RequestHandler->isAjax())
		{
			$isajax=1;
			$cotdachon = $this->data['Z1sinhvien']['dscoldiem'];
			
			if(!empty($cotdachon))
			{
				$cotdachon = array_flip($cotdachon);
				$cothienthi =array_diff_key($this->Z1sinhvien->dscoldiem,array_diff_key($this->Z1sinhvien->dscoldiem,$cotdachon));
			}
			else
			{
				$cothienthi = $this->Z1sinhvien->dscoldiem;
			}
			
			$conditions = array();
			if($this->data['Z1sinhvien']['tendantoc']!=null)
				$conditions['Z1sinhvien.dantoc_id']=$this->data['Z1sinhvien']['tendantoc'];
			if($this->data['Z1sinhvien']['nganh']!=null)
				$conditions['Z1sinhvien.nganh_id']=$this->data['Z1sinhvien']['nganh'];
			if($this->data['Z1sinhvien']['tentinhtp']!=null)
				$conditions['Z1sinhvien.tinhtp_id']=$this->data['Z1sinhvien']['tentinhtp'];
			if($this->data['Z1sinhvien']['khoahoc']!=null)
				$conditions['Z1sinhvien.khoahoc_id']=$this->data['Z1sinhvien']['khoahoc'];
			if($this->data['Z1sinhvien']['phai']!=null)
				$conditions['Z1sinhvien.phai']=$this->data['Z1sinhvien']['phai'];
			// doan or dang	
			if($this->data['Z1sinhvien']['doituong']!=null)
				if($this->data['Z1sinhvien']['doituong']==0)
				{
					$conditions['AND']=array(array('ngayvaodoan !='=>null),
											array('ngayvaodoan !='=>'0000-00-00'),
											'OR'=>array(
												array('ngayvaodang'=>null),
												array('ngayvaodang'=>'0000-00-00')));
				}
				else
				{
					$conditions['AND']=array(array('ngayvaodoan !='=>null),
													array('ngayvaodoan !='=>'0000-00-00'),
													array('ngayvaodang !='=>null),
													array('ngayvaodang !='=>'0000-00-00'));
				}
			if(!empty($this->data['Z1sinhvien']['hocky']))
				$conditions['hocky_id']=$this->data['Z1sinhvien']['hocky'];
			else
			{
				$nh = $this->data['Z1sinhvien']['namhoc'];
				$hk = $this->Hocky->find('list',array('conditions'=>array('namhoc_id'=>$nh)));
				$conditions['hocky_id']=$hk;
			}
			$joins =array(array('table' => 'phanloaisvs',
            						'type' => 'INNER',
            						'alias' => 'Phanloaisv',
            						'conditions' => array(
                					'Z1sinhvien.id = Phanloaisv.sinhvien_id')));
           	//'Z1sinhvien.*'
            $query = $this->Z1sinhvien->generatequery(array('fields'=>array('Phanloaisv.sinhvien_id',
            														'round(AVG(Phanloaisv.diemtbhk),2) as diemtb',
            														'round(AVG(Phanloaisv.diemrenluyen),2) as diemrenluyen'),
            												'conditions'=>$conditions,
            												'joins'=>$joins,'group'=>array('Phanloaisv.sinhvien_id')));            
            												
			if($this->data['Z1sinhvien']['loai']=='avg')
			{
				$joins =array(array('table' => '('.$query.')'
            						,'type' => 'INNER'
            						,'alias' => 'diemsinhvien'
            						,'conditions' => array(
                					'Z1sinhvien.id = diemsinhvien.sinhvien_id')));
				$this->paginate = array('fields'=>array('Z1sinhvien.*','diemsinhvien.*'),
														'joins'=>$joins,
																//'limit'=>2,
														'order'=>array('diemsinhvien.diemtb desc','diemsinhvien.diemrenluyen desc'));
				$sinhviens = $this->paginate();
			}
			elseif($this->data['Z1sinhvien']['loai']=='max')
			{
				$joins =array(array('table' => '('.$query.')',
            						'type' => 'INNER',
            						'alias' => 'diemsinhvien',
            						'conditions' => array(
                					'Z1sinhvien.id = diemsinhvien.sinhvien_id')));
            	$query1 = $this->Z1sinhvien->generatequery(array('fields'=>array('max(diemsinhvien.diemtb) as maxdiemtb'),
            													'joins'=>$joins));
            	$joins =array(array('table' => '('.$query.')',
            						'type' => 'INNER',
            						'alias' => 'diemsinhvien',
            						'conditions' => array('Z1sinhvien.id = diemsinhvien.sinhvien_id',
            												'diemsinhvien.diemtb IN('.$query1.')')));
				$this->paginate = array('fields'=>array('Z1sinhvien.*','diemsinhvien.*'),
														'joins'=>$joins,
																//'limit'=>2,
														'order'=>array('diemsinhvien.diemtb desc','diemsinhvien.diemrenluyen desc'));
				$sinhviens = $this->paginate();
			}
			else
			{
				$loaiss = $this->data['Z1sinhvien']['loai'];
				$giatri =(empty($this->data['Z1sinhvien']['giatri'])?0:$this->data['Z1sinhvien']['giatri']);
				$joins =array(array('table' => '('.$query.')',
            						'type' => 'INNER',
            						'alias' => 'diemsinhvien',
            						'conditions' => array(
                					'Z1sinhvien.id = diemsinhvien.sinhvien_id','diemsinhvien.diemtb '.$loaiss=>$giatri)));
				$this->paginate = array('fields'=>array('Z1sinhvien.*','diemsinhvien.*'),
														'joins'=>$joins,
																//'limit'=>2,
														'order'=>array('diemsinhvien.diemtb desc','diemsinhvien.diemrenluyen desc'));
				$sinhviens = $this->paginate();
				
			}
			
			$name ='datasearchdiem'.strtotime(date('h:i:s'));
			$this->Session->write($name,$this->data);
			$this->set(compact('cothienthi','name','sinhviens'));
		}
		else
		{
			if(!empty($this->params['named']['page']) && $name)
			{
				$this->data = $this->Session->read($name);
				if(!empty($this->data))
				{
					$cotdachon = $this->data['Z1sinhvien']['dscoldiem'];
					
					if(!empty($cotdachon))
					{
						$cotdachon = array_flip($cotdachon);
						$cothienthi =array_diff_key($this->Z1sinhvien->dscoldiem,array_diff_key($this->Z1sinhvien->dscoldiem,$cotdachon));
					}
					else
					{
						$cothienthi = $this->Z1sinhvien->dscoldiem;
					}
					
					$conditions = array();
					if($this->data['Z1sinhvien']['tendantoc']!=null)
						$conditions['Z1sinhvien.dantoc_id']=$this->data['Z1sinhvien']['tendantoc'];
					if($this->data['Z1sinhvien']['nganh']!=null)
						$conditions['Z1sinhvien.nganh_id']=$this->data['Z1sinhvien']['nganh'];
					if($this->data['Z1sinhvien']['tentinhtp']!=null)
						$conditions['Z1sinhvien.tinhtp_id']=$this->data['Z1sinhvien']['tentinhtp'];
					if($this->data['Z1sinhvien']['khoahoc']!=null)
						$conditions['Z1sinhvien.khoahoc_id']=$this->data['Z1sinhvien']['khoahoc'];
					if($this->data['Z1sinhvien']['phai']!=null)
						$conditions['Z1sinhvien.phai']=$this->data['Z1sinhvien']['phai'];
					// doan or dang	
					if($this->data['Z1sinhvien']['doituong']!=null)
						if($this->data['Z1sinhvien']['doituong']==0)
						{
							$conditions['AND']=array(array('ngayvaodoan !='=>null),
													array('ngayvaodoan !='=>'0000-00-00'),
													'OR'=>array(
														array('ngayvaodang'=>null),
														array('ngayvaodang'=>'0000-00-00')));
						}
						else
						{
							$conditions['AND']=array(array('ngayvaodoan !='=>null),
															array('ngayvaodoan !='=>'0000-00-00'),
															array('ngayvaodang !='=>null),
															array('ngayvaodang !='=>'0000-00-00'));
						}
					if(!empty($this->data['Z1sinhvien']['hocky']))
						$conditions['hocky_id']=$this->data['Z1sinhvien']['hocky'];
					else
					{
						$nh = $this->data['Z1sinhvien']['namhoc'];
						$hk = $this->Hocky->find('list',array('conditions'=>array('namhoc_id'=>$nh)));
						$conditions['hocky_id']=$hk;
					}
					$joins =array(array('table' => 'phanloaisvs',
		            						'type' => 'INNER',
		            						'alias' => 'Phanloaisv',
		            						'conditions' => array(
		                					'Z1sinhvien.id = Phanloaisv.sinhvien_id')));
		           	//'Z1sinhvien.*'
		            $query = $this->Z1sinhvien->generatequery(array('fields'=>array('Phanloaisv.sinhvien_id',
		            														'round(AVG(Phanloaisv.diemtbhk),2) as diemtb',
		            														'round(AVG(Phanloaisv.diemrenluyen),2) as diemrenluyen'),
		            												'conditions'=>$conditions,
		            												'joins'=>$joins,'group'=>array('Phanloaisv.sinhvien_id')));            
		            												
					if($this->data['Z1sinhvien']['loai']=='avg')
					{
						$joins =array(array('table' => '('.$query.')'
		            						,'type' => 'INNER'
		            						,'alias' => 'diemsinhvien'
		            						,'conditions' => array(
		                					'Z1sinhvien.id = diemsinhvien.sinhvien_id')));
						$this->paginate = array('fields'=>array('Z1sinhvien.*','diemsinhvien.*'),
																'joins'=>$joins,
																//'limit'=>2,
																'order'=>array('diemsinhvien.diemtb desc','diemsinhvien.diemrenluyen desc'));
						$sinhviens = $this->paginate();
					}
					elseif($this->data['Z1sinhvien']['loai']=='max')
					{
						$joins =array(array('table' => '('.$query.')',
		            						'type' => 'INNER',
		            						'alias' => 'diemsinhvien',
		            						'conditions' => array(
		                					'Z1sinhvien.id = diemsinhvien.sinhvien_id')));
		            	$query1 = $this->Z1sinhvien->generatequery(array('fields'=>array('max(diemsinhvien.diemtb) as maxdiemtb'),
		            													'joins'=>$joins));
		            	$joins =array(array('table' => '('.$query.')',
		            						'type' => 'INNER',
		            						'alias' => 'diemsinhvien',
		            						'conditions' => array('Z1sinhvien.id = diemsinhvien.sinhvien_id',
		            												'diemsinhvien.diemtb IN('.$query1.')')));
						$this->paginate = array('fields'=>array('Z1sinhvien.*','diemsinhvien.*'),
																'joins'=>$joins,
																//'limit'=>2,
																'order'=>array('diemsinhvien.diemtb desc','diemsinhvien.diemrenluyen desc'));
						$sinhviens = $this->paginate();
					}
					else
					{
						$loaiss = $this->data['Z1sinhvien']['loai'];
						$giatri =(empty($this->data['Z1sinhvien']['giatri'])?0:$this->data['Z1sinhvien']['giatri']);
						$joins =array(array('table' => '('.$query.')',
		            						'type' => 'INNER',
		            						'alias' => 'diemsinhvien',
		            						'conditions' => array(
		                					'Z1sinhvien.id = diemsinhvien.sinhvien_id','diemsinhvien.diemtb '.$loaiss=>$giatri)));
						$this->paginate = array('fields'=>array('Z1sinhvien.*','diemsinhvien.*'),
																'joins'=>$joins,
																//'limit'=>2,
																'order'=>array('diemsinhvien.diemtb desc','diemsinhvien.diemrenluyen desc'));
						$sinhviens = $this->paginate();
						
					}
				}
				$this->set(compact('sinhviens'));	
			}
			$this->Dantoc->displayField='tendantoc';
			$dantocs = $this->Dantoc->find('list');
			$this->Tinhtp->displayField='tentinhtp';
			$quequans = $this->Tinhtp->find('list');
			$dscoldiem = $this->Z1sinhvien->dscoldiem;
			$this->Nganh->displayField='tennganh';
			$nganhs = $this->Nganh->find('list',array('conditions'=>array('tennganh !='=>null)));
			$this->Khoahoc->displayField='nambatdau';
			$khoahocs = $this->Khoahoc->find('list');
			$this->Xeploai->displayField = 'tenloai';
			$xeploais = $this->Xeploai->find('list');
			$this->Namhoc->displayField='namhoc';
			$namhocs = $this->Namhoc->find('list');
			if(!empty($this->data['Z1sinhvien']['namhoc']))
			{
				$this->Hocky->displayField='tenhocky';
				$hockys = $this->Hocky->find('list',array('conditions'=>array('namhoc_id'=>$this->data['Z1sinhvien']['namhoc'])));
			}
			$this->set(compact('dantocs','quequans','dscoldiem','nganhs','khoahocs','cothienthi','namhocs','xeploais','hockys','name'));
		}
		$this->set(compact('isajax'));
	}

	function xuatexceldiem($name=null)
	{
		$this->layout='';
		if($name)
		{
				$this->data = $this->Session->read($name);
				if(!empty($this->data))
				{
					$cotdachon = $this->data['Z1sinhvien']['dscoldiem'];
					
					if(!empty($cotdachon))
					{
						$cotdachon = array_flip($cotdachon);
						$cothienthi =array_diff_key($this->Z1sinhvien->dscoldiem,array_diff_key($this->Z1sinhvien->dscoldiem,$cotdachon));
					}
					else
					{
						$cothienthi = $this->Z1sinhvien->dscoldiem;
					}
					
					$conditions = array();
					if($this->data['Z1sinhvien']['tendantoc']!=null)
						$conditions['Z1sinhvien.dantoc_id']=$this->data['Z1sinhvien']['tendantoc'];
					if($this->data['Z1sinhvien']['nganh']!=null)
						$conditions['Z1sinhvien.nganh_id']=$this->data['Z1sinhvien']['nganh'];
					if($this->data['Z1sinhvien']['tentinhtp']!=null)
						$conditions['Z1sinhvien.tinhtp_id']=$this->data['Z1sinhvien']['tentinhtp'];
					if($this->data['Z1sinhvien']['khoahoc']!=null)
						$conditions['Z1sinhvien.khoahoc_id']=$this->data['Z1sinhvien']['khoahoc'];
					if($this->data['Z1sinhvien']['phai']!=null)
						$conditions['Z1sinhvien.phai']=$this->data['Z1sinhvien']['phai'];
					// doan or dang	
					if($this->data['Z1sinhvien']['doituong']!=null)
						if($this->data['Z1sinhvien']['doituong']==0)
						{
							$conditions['AND']=array(array('ngayvaodoan !='=>null),
													array('ngayvaodoan !='=>'0000-00-00'),
													'OR'=>array(
														array('ngayvaodang'=>null),
														array('ngayvaodang'=>'0000-00-00')));
						}
						else
						{
							$conditions['AND']=array(array('ngayvaodoan !='=>null),
															array('ngayvaodoan !='=>'0000-00-00'),
															array('ngayvaodang !='=>null),
															array('ngayvaodang !='=>'0000-00-00'));
						}
					if(!empty($this->data['Z1sinhvien']['hocky']))
						$conditions['hocky_id']=$this->data['Z1sinhvien']['hocky'];
					else
					{
						$nh = $this->data['Z1sinhvien']['namhoc'];
						$hk = $this->Hocky->find('list',array('conditions'=>array('namhoc_id'=>$nh)));
						$conditions['hocky_id']=$hk;
					}
					$joins =array(array('table' => 'phanloaisvs',
		            						'type' => 'INNER',
		            						'alias' => 'Phanloaisv',
		            						'conditions' => array(
		                					'Z1sinhvien.id = Phanloaisv.sinhvien_id')));
		           	//'Z1sinhvien.*'
		            $query = $this->Z1sinhvien->generatequery(array('fields'=>array('Phanloaisv.sinhvien_id',
		            														'round(AVG(Phanloaisv.diemtbhk),2) as diemtb',
		            														'round(AVG(Phanloaisv.diemrenluyen),2) as diemrenluyen'),
		            												'conditions'=>$conditions,
		            												'joins'=>$joins,'group'=>array('Phanloaisv.sinhvien_id')));            
		            												
					if($this->data['Z1sinhvien']['loai']=='avg')
					{
						$joins =array(array('table' => '('.$query.')'
		            						,'type' => 'INNER'
		            						,'alias' => 'diemsinhvien'
		            						,'conditions' => array(
		                					'Z1sinhvien.id = diemsinhvien.sinhvien_id')));

						$sinhviens = $this->Z1sinhvien->find('all',array('fields'=>array('Z1sinhvien.*','diemsinhvien.*'),
																'joins'=>$joins,
																'order'=>array('diemsinhvien.diemtb desc','diemsinhvien.diemrenluyen desc')));
					}
					elseif($this->data['Z1sinhvien']['loai']=='max')
					{
						$joins =array(array('table' => '('.$query.')',
		            						'type' => 'INNER',
		            						'alias' => 'diemsinhvien',
		            						'conditions' => array(
		                					'Z1sinhvien.id = diemsinhvien.sinhvien_id')));
		            	$query1 = $this->Z1sinhvien->generatequery(array('fields'=>array('max(diemsinhvien.diemtb) as maxdiemtb'),
		            													'joins'=>$joins));
		            	$joins =array(array('table' => '('.$query.')',
		            						'type' => 'INNER',
		            						'alias' => 'diemsinhvien',
		            						'conditions' => array('Z1sinhvien.id = diemsinhvien.sinhvien_id',
		            												'diemsinhvien.diemtb IN('.$query1.')')));
						$sinhviens = $this->Z1sinhvien->find('all', array('fields'=>array('Z1sinhvien.*','diemsinhvien.*'),
																'joins'=>$joins,
																'order'=>array('diemsinhvien.diemtb desc','diemsinhvien.diemrenluyen desc')));
					}
					else
					{
						$loaiss = $this->data['Z1sinhvien']['loai'];
						$giatri =(empty($this->data['Z1sinhvien']['giatri'])?0:$this->data['Z1sinhvien']['giatri']);
						$joins =array(array('table' => '('.$query.')',
		            						'type' => 'INNER',
		            						'alias' => 'diemsinhvien',
		            						'conditions' => array(
		                					'Z1sinhvien.id = diemsinhvien.sinhvien_id','diemsinhvien.diemtb '.$loaiss=>$giatri)));
						$sinhviens = $this->Z1sinhvien->find('all', array('fields'=>array('Z1sinhvien.*','diemsinhvien.*'),
																'joins'=>$joins,
																'order'=>array('diemsinhvien.diemtb desc','diemsinhvien.diemrenluyen desc')));						
					}
				}
				$this->set(compact('sinhviens','cothienthi'));	
			}
	}
}
